//
// Copyright (C) 2008 Jackson Harper (jackson@ximian.com)

// This program is free software; you can redistribute it and/or
// modify it under the terms of the GNU General Public License
// as published by the Free Software Foundation; either version 2
// of the License, or (at your option) any later version.

// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.

// You should have received a copy of the GNU General Public License
// along with this program; if not, write to the Free Software
// Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
//
//

using System;
using System.Data;
using System.Configuration;
using System.Collections.Generic;

using MySql.Data.Types;
using MySql.Data.MySqlClient;

namespace Grr.Model {

	public class Grurrah : DbEntity {

		private long id;

		private int rating = 0;
		private string [] tags;
		private string description_body;

		private static readonly int GrurrahIdColumn = 0;
		private static readonly int GrurrahNameColumn = 1;
		private static readonly int EntryTextColumn = 2;
		private static readonly int TagNameColumn = 3;
		private static readonly int RatingAvgColumn = 4;

		private static readonly string SelectStatement = "SELECT Grurrahs.id, Grurrahs.name, Revisions.text, Tags.name, AVG(user_grurrah_ratings.rating) FROM Grurrahs " +
				"RIGHT JOIN Revisions ON Grurrahs.revision_id = Revisions.id " +
				"LEFT JOIN tag_map ON Grurrahs.id = tag_map.grurrah_id LEFT JOIN Tags on tag_map.tag_id = Tags.id " +
				"LEFT JOIN user_grurrah_ratings ON Grurrahs.id = user_grurrah_ratings.grurrah_id";

		private static readonly string GroupByStatement = "GROUP BY Grurrahs.id, Tags.name";


		// For cereal
		public Grurrah () { }

		private Grurrah (string name) : base (name)
		{
		}

		private Grurrah (long id, string name, string description_body) : base (name)
		{
			this.id = id;
			this.description_body = description_body;
		}

        public long Id
        {
            get { return id; }
        }

		public int Rating {
			get { return rating; }
		}

		public string [] Tags {
			get {
				if (tags == null)
					tags = new string [0];
				return tags;
			}
		}

		public string DescriptionBody {
			get { return description_body; }
		}

        public void UpdateDescriptionText(string desc, string user, string user_ip)
        {
            if (desc == description_body)
                return;

            UpdateDescriptionText(id, desc, user, user_ip);
            description_body = desc;
        }
        
        public static void UpdateDescriptionText (long grurrah_id, string desc, string user, string user_ip)
        {
            desc = HtmlCleaner.CleanHtml(desc);
            EntityFlags flags = SpamChecker.GetIsSpamFlags (desc);

			using (MySqlConnection con = OpenReadWriteConnection ()) {
                MySqlCommand icmd = new MySqlCommand("insert into Revisions (grurrah_id, creator_id, creator_ip, created_time, flags, text) values (?grurrah_id, ?creator_id, INET_ATON(?creator_ip), NOW(), ?flags, ?text)", con);
				icmd.Parameters.Add ("?grurrah_id", MySqlDbType.Int64).Value = grurrah_id;
                icmd.Parameters.Add ("?creator_id", MySqlDbType.String).Value = user;
                icmd.Parameters.Add ("?creator_ip", MySqlDbType.String).Value = user_ip;
                icmd.Parameters.Add ("?flags", MySqlDbType.Int32).Value = (int)flags;
				icmd.Parameters.Add ("?text", MySqlDbType.Text).Value = desc;

				if (icmd.ExecuteNonQuery() == 0)
					throw new Exception ("Unable to insert text into table.");

                MySqlCommand ucmd = new MySqlCommand("UPDATE Grurrahs SET revision_id = LAST_INSERT_ID() WHERE id = ?grurrah_id", con);
				ucmd.Parameters.Add ("?grurrah_id", MySqlDbType.Int64).Value = grurrah_id;

				if (ucmd.ExecuteNonQuery () == 0)
					throw new Exception ("Unable to update Grurrah revision id.");
			}
		}

		public void SetTags (string[] tags)
		{
			List<string> new_tags = new List<string> (tags.Length);
			List<string> old_tags = new List<string> (this.tags);

			foreach (string tag in tags)
				new_tags.Add (tag.Trim ());

			foreach (string tag in new_tags) {
				if (tag.Length < 1 || old_tags.Contains (tag))
					continue;
				AddTag (tag, false);
			}

			foreach (string tag in old_tags) {
				if (tag.Length < 1 || new_tags.Contains (tag))
					continue;
				RemoveTag (tag, false);
			}

			this.tags = new_tags.ToArray ();
		}

		public void AddTag (string str, bool update_local)
		{
			long tag_id = Tag.GetTagId (str);

			using (MySqlConnection rcon = OpenReadWriteConnection ()) {
				MySqlCommand qcmd = new MySqlCommand ("select id FROM tag_map WHERE grurrah_id = ?grurrah_id AND tag_id = ?tag_id", rcon);
				qcmd.Parameters.Add ("?grurrah_id", MySqlDbType.Int64).Value = id;
				qcmd.Parameters.Add ("?tag_id", MySqlDbType.Int64).Value = tag_id;

				MySqlDataReader reader = qcmd.ExecuteReader ();
				if (reader.Read ()) {
					Console.WriteLine ("already there!");
					return;
				}

				using (MySqlConnection rwcon = OpenReadWriteConnection ()) {
					MySqlCommand icmd = new MySqlCommand ("INSERT INTO tag_map (grurrah_id, tag_id) VALUES (?grurrah_id, ?tag_id)", rwcon);
					icmd.Parameters.Add ("?grurrah_id", MySqlDbType.Int64).Value = id;
					icmd.Parameters.Add ("?tag_id", MySqlDbType.Int64).Value = tag_id;

					icmd.ExecuteNonQuery ();
				}
			}

			if (update_local) {
				int last = tags.Length;
				Array.Resize<string> (ref tags, tags.Length + 1);
				tags [last] = str;
			}
		}

		public void RemoveTag (string tag, bool update_local)
		{
			long tag_id = Tag.GetTagId (tag);

			using (MySqlConnection rwcon = OpenReadWriteConnection ()) {
				MySqlCommand icmd = new MySqlCommand ("DELETE FROM tag_map WHERE grurrah_id = ?grurrah_id AND tag_id = ?tag_id", rwcon);
				icmd.Parameters.Add ("?grurrah_id", MySqlDbType.Int64).Value = id;
				icmd.Parameters.Add ("?tag_id", MySqlDbType.Int64).Value = tag_id;

				icmd.ExecuteNonQuery ();
			}

			if (update_local) {
				string [] new_tags = new string [tags.Length - 1];
				int ni = 0;

				for (int oi = 0; oi < tags.Length; oi++) {
					if (tags [oi] != tag)
						new_tags [ni++] = tags [oi];
				}

				tags = new_tags;
			}
		}

        public UserGrurrahStatus GetStatusForUser(string user)
        {
            return GetStatusForUser(id, user);
        }

		public static UserGrurrahStatus GetStatusForUser (long grurrah_id, string user)
		{
			using (MySqlConnection con = OpenReadConnection ()) {
				MySqlCommand cmd = new MySqlCommand ("select status from user_grurrah_status WHERE grurrah_id = ?grurrah_id AND user_id = ?user", con);
				cmd.Parameters.Add ("?grurrah_id", MySqlDbType.Int64).Value = grurrah_id;
				cmd.Parameters.Add ("?user", MySqlDbType.VarChar, 36).Value = user;

				UserGrurrahStatus res = UserGrurrahStatus.None;
				object o = cmd.ExecuteScalar ();
				if (o != null)
					res = (UserGrurrahStatus) o;
				return res;
			}
		}

        public void SetStatusForUser(string user, UserGrurrahStatus status)
        {
            SetStatusForUser(id, user, status);
        }

		public static void SetStatusForUser(long grurrah_id, string user, UserGrurrahStatus status)
		{
			using (MySqlConnection rcon = OpenReadConnection ()) {
				MySqlCommand cmd = new MySqlCommand ("select status from user_grurrah_status WHERE grurrah_id = ?grurrah_id AND user_id = ?user", rcon);
				cmd.Parameters.Add ("?grurrah_id", MySqlDbType.Int64).Value = grurrah_id;
				cmd.Parameters.Add ("?user", MySqlDbType.VarChar, 36).Value = user;

				MySqlDataReader reader = cmd.ExecuteReader ();

				using (MySqlConnection rwcon = OpenReadWriteConnection ()) {
					if (!reader.Read ())
						cmd = new MySqlCommand ("INSERT INTO user_grurrah_status (user_id, grurrah_id, status) VALUES (?user, ?grurrah_id, ?status)", rwcon);
					else
						cmd = new MySqlCommand ("UPDATE user_grurrah_status SET status = ?status WHERE user_id = ?user AND grurrah_id = ?grurrah_id", rwcon);

					cmd.Parameters.Add ("?grurrah_id", MySqlDbType.Int64).Value = grurrah_id;
					cmd.Parameters.Add ("?user", MySqlDbType.VarChar, 36).Value = user;
					cmd.Parameters.Add ("?status", MySqlDbType.Int32).Value = (int) status;

					cmd.ExecuteNonQuery ();
				}
			}
		}

		public static Grurrah QueryForByName (string name)
		{
			if (name == null)
				return null;

			Grurrah res = null;
			using (MySqlConnection con = OpenReadConnection ()) {
				MySqlCommand cmd = new MySqlCommand ();
				string where_statement = "Grurrahs.name = ?name";

				cmd.Connection = con;
				cmd.Parameters.Add("?name", MySqlDbType.VarChar, 60).Value = GrurrahString.ToDbString (name);

				res = RunQuery (cmd, where_statement);
			}
            
			return res;
		}

        public static Grurrah Create(string name, string text, string creator_id, string creator_ip)
		{
			using (MySqlConnection con = OpenReadWriteConnection ()) {
				MySqlCommand cmd = new MySqlCommand ("INSERT INTO Grurrahs (name, entry_index, created_time, updated_time, creator) VALUES (?name, 1, NOW(), NOW(), ?creator_id)", con);
				cmd.Parameters.Add ("?name", MySqlDbType.VarChar, 60).Value = name;
				cmd.Parameters.Add ("?creator_id", MySqlDbType.VarChar, 36).Value = creator_id;

				cmd.ExecuteNonQuery ();

				cmd = new MySqlCommand ("SELECT LAST_INSERT_ID()", con);
				long grurrah_id = (long) cmd.ExecuteScalar ();

                UpdateDescriptionText(grurrah_id, text, creator_id, creator_ip);
                return new Grurrah (grurrah_id, name, text);
			}
		}

		public static List<string> GetTenHighestRatedNames ()
		{
			using (MySqlConnection con = OpenReadConnection ()) {
				MySqlCommand cmd = new MySqlCommand ("select Grurrahs.name, AVG(user_grurrah_ratings.rating) FROM Grurrahs " +
						"LEFT JOIN user_grurrah_ratings ON Grurrahs.id = user_grurrah_ratings.grurrah_id GROUP BY Grurrahs.name " +
						"ORDER BY AVG(user_grurrah_ratings.rating) DESC LIMIT 0,10", con);
				MySqlDataReader reader = cmd.ExecuteReader ();

				List<string> res = new List<string> ();
				while (reader.Read ())
					res.Add (reader.GetString ("name"));
				return res;
			}
		}

		public static List<string> GetTenHighestRatedNamesForTag (string tag)
		{
			using (MySqlConnection con = OpenReadConnection ()) {
				MySqlCommand cmd = new MySqlCommand ("select Grurrahs.name, Tags.name, AVG(user_grurrah_ratings.rating) FROM Grurrahs " +
						"LEFT JOIN tag_map ON tag_map.grurrah_id = Grurrahs.id LEFT JOIN Tags ON Tags.id = tag_map.tag_id " +
						"LEFT JOIN user_grurrah_ratings ON user_grurrah_ratings.grurrah_id = Grurrahs.id " +
						"WHERE Tags.name = ?tag_name GROUP BY Grurrahs.name ORDER BY AVG(user_grurrah_ratings.rating) DESC LIMIT 0,10", con);
				cmd.Parameters.Add ("?tag_name", MySqlDbType.VarChar, 100).Value = tag;
            
				MySqlDataReader reader = cmd.ExecuteReader ();
				List<string> res = new List<string> ();
				while (reader.Read ())
					res.Add (reader.GetString ("name"));
				return res;
			}
		}

		public static List<string> GetTenMostRecentlyCreatedNames ()
		{
			using (MySqlConnection con = OpenReadConnection ()) {
				MySqlCommand cmd = new MySqlCommand ("select Grurrahs.name FROM Grurrahs ORDER BY created_time DESC LIMIT 0,10", con);
				MySqlDataReader reader = cmd.ExecuteReader ();

				List<string> res = new List<string> (10);
				while (reader.Read ())
					res.Add (reader.GetString ("name"));
				return res;
			}
		}

		public static List<string> GetTenMostRecentlyUpdatedNames ()
		{
			using (MySqlConnection con = OpenReadConnection ()) {
				MySqlCommand cmd = new MySqlCommand ("select Grurrahs.name FROM Grurrahs ORDER BY updated_time LIMIT 0,10", con);
				MySqlDataReader reader = cmd.ExecuteReader ();

				List<string> res = new List<string> (10);
				while (reader.Read ())
					res.Add (reader.GetString ("name"));
				return res;
			}
		}

		public void SubmitRating (GrurrahUser user, int rating)
		{
			using (MySqlConnection rwcon = OpenReadWriteConnection ()) {
				MySqlCommand qcmd = new MySqlCommand ("select rating from user_grurrah_ratings where grurrah_id = ?grurrah_id AND user_id = ?user_id", rwcon);
				qcmd.Parameters.Add ("?grurrah_id", MySqlDbType.Int64).Value = id;
				qcmd.Parameters.Add ("?user_id", MySqlDbType.VarChar, 36).Value = user.Name;

				int old_rating = 0;
				object o = qcmd.ExecuteScalar ();
				if (o != null)
					old_rating = (int) o;

				if (old_rating == rating)
					return;

				MySqlCommand ucmd = null;
				if (old_rating != 0)
					ucmd = new MySqlCommand ("UPDATE user_grurrah_ratings SET rating = ?rating WHERE user_id = ?user_id AND grurrah_id = ?grurrah_id", rwcon);
				else
					ucmd = new MySqlCommand ("INSERT INTO user_grurrah_ratings (user_id, grurrah_id, rating) VALUES (?user_id, ?grurrah_id, ?rating)", rwcon);
                
				ucmd.Parameters.Add ("?grurrah_id", MySqlDbType.Int64).Value = id;
				ucmd.Parameters.Add ("?user_id", MySqlDbType.VarChar, 36).Value = user.Name;
				ucmd.Parameters.Add ("?rating", MySqlDbType.Int32).Value = rating;

				ucmd.ExecuteNonQuery ();
			}
		}

		public void ClearRating (GrurrahUser user)
		{
			using (MySqlConnection rwcon = OpenReadWriteConnection ()) {
				MySqlCommand dcmd = new MySqlCommand ("DELETE FROM user_grurrah_ratings WHERE user_id = ?user_id AND grurrah_id = ?grurrah_id", rwcon);
				dcmd.Parameters.Add ("?grurrah_id", MySqlDbType.Int64).Value = id;
				dcmd.Parameters.Add ("?user_id", MySqlDbType.VarChar, 36).Value = user.Name;

				dcmd.ExecuteNonQuery ();
			}
		}

		private static Grurrah RunQuery (MySqlCommand cmd, string where_statement)
		{
			cmd.CommandText = String.Format ("{0} WHERE {1} {2}", SelectStatement, where_statement, GroupByStatement);
			MySqlDataReader reader = cmd.ExecuteReader ();

			if (!reader.Read ())
				return null;

			long id = reader.GetInt64 (GrurrahIdColumn);
			string name = reader.GetString (GrurrahNameColumn);
			string text = reader.GetString (EntryTextColumn);

			Grurrah res = new Grurrah (id, name, text);
			if (reader [RatingAvgColumn] != DBNull.Value)
				res.rating = reader.GetInt32 (RatingAvgColumn);

			List<string> tags = new List<string> ();

			do
			{
				if (reader [TagNameColumn] == DBNull.Value)
					continue;
				string tag = reader.GetString (TagNameColumn);
				if (!tags.Contains (tag))
					tags.Add (tag);
			} while (reader.Read ());
			res.tags = tags.ToArray ();

			return res;
		}

        /*
        public static void UpdateEntriesToRevisions()
        {
            using (MySqlConnection con = OpenReadConnection())
            {
                MySqlCommand gcmd = new MySqlCommand("SELECT Grurrahs.id, Entries.id FROM Grurrahs " +
				                                     "JOIN Entries ON Grurrahs.id = Entries.grurrah_id AND " +
                                                     "Grurrahs.entry_index = Entries.entry_index", con);
                MySqlDataReader gread = gcmd.ExecuteReader();

                while (gread.Read())
                {
                    long grurrah_id = gread.GetInt64(0);
                    long revision_id = gread.GetInt64(1);

                    using (MySqlConnection ucon = OpenReadWriteConnection())
                    {
                        MySqlCommand cmd = new MySqlCommand("UPDATE Grurrahs SET revision_id=?revision_id WHERE id = ?grurrah_id", ucon);
                        cmd.Parameters.Add("grurrah_id", MySqlDbType.Int64).Value = grurrah_id;
                        cmd.Parameters.Add("revision_id", MySqlDbType.Int64).Value = revision_id;
                        
                        if (cmd.ExecuteNonQuery () != 1) {
                            Console.Error.WriteLine("ERROR!");
                        }
                    }
                }

            }
        }
         */
	}
}
